package com.elong.qadev.tool;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;
import org.hibernate.engine.jdbc.internal.BasicFormatterImpl;
import org.hibernate.engine.jdbc.internal.Formatter;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.elong.qadev.entity.DBLink;
import com.elong.qadev.entity.DBSql;
import com.elong.qadev.service.HttpCaseServiceI;
/**
 * 
* @ClassName: DBUtils
* @Description: TODO(用例执行DB提取数据的执行类)
* @author kangchen
* @date 2015年9月30日
*
 */
public class DBUtils {
	static Logger logger = Logger.getLogger(DBUtils.class);
	public static HttpCaseServiceI httpCaseService;

    private static final String properties = "useUnicode=true;characterEncoding=utf-8;"
            + "zeroDateTimeBehavior=convertToNull;transformedBitIsBoolean=true;autoReconnect=true;"
            + "failOverReadOnly=false;maxReconnects=10;initialTimeout=2;"
            + "connectTimeout=0;socketTimeout=0";

    public static List<String[]> querySQL(String sqlId) throws Exception {
        return querySQL(sqlId, null);
    }

    public static List<String[]> querySQL(String sqlId,
            Map<Integer, String> meta) throws Exception {
    	ApplicationContext ac = new ClassPathXmlApplicationContext(
				new String[] { "spring.xml", "spring-hibernate.xml" });
		httpCaseService = (HttpCaseServiceI) ac.getBean("httpCaseService");
		DBSql dbSql = httpCaseService.getDBSqlById(Integer.parseInt(sqlId.split("-")[1]));
		DBLink dbLink = httpCaseService.getDBLinkById(dbSql.getDbLinkId());
		String driverClassName = "";
		String url = "";
		if(dbLink.getType().equalsIgnoreCase("mysql")){
			driverClassName="com.mysql.jdbc.Driver";
			url="jdbc:mysql://"+dbLink.getIp()+":"+dbLink.getPort()+"/"+dbLink.getDbName()+"";
		}else if(dbLink.getType().equalsIgnoreCase("sqlserver")){
			driverClassName="net.sourceforge.jtds.jdbc.Driver";
			url="jdbc:jtds:sqlserver://"+dbLink.getIp()+";DatabaseName="+dbLink.getDbName()+"";
		}
        String username = dbLink.getUserName();
        String password = dbLink.getPassword();
        String sql = dbSql.getSql();
        List<String[]> results = DBUtils.querySQL(driverClassName, url,
                username, password, sql, meta);
        return results;
    }

    public static List<String[]> querySQL(String driverClassName, String url,
            String username, String password, String sql) throws Exception {
        return querySQL(driverClassName, url, username, password, sql, null);
    }

    public static List<String[]> querySQL(String driverClassName, String url,
            String username, String password, String sql,
            Map<Integer, String> meta) throws Exception {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(driverClassName);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setConnectionProperties(properties);
        return querySQL(dataSource, sql, meta);
    }
    
    public static List<String[]> querySQL(BasicDataSource dataSource, String sql,
            Map<Integer, String> meta) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        List<String[]> results = new ArrayList<String[]>();

        try {
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            Formatter f = new BasicFormatterImpl();
            try {
                logger.info(String.format("执行SQL语句: %s", f.format(sql)));
            } catch (Exception ex) {
                logger.info(String.format("执行SQL语句：%s", sql));
            }
            rset = stmt.executeQuery(sql);
            int numcols = rset.getMetaData().getColumnCount();
            if (null != meta) {
                ResultSetMetaData rsmd = rset.getMetaData();
                for (int i = 1; i <= numcols; ++i) {
                    meta.put(i - 1, rsmd.getColumnLabel(i));
                }
            }
            while (rset.next()) {
                String[] result = new String[numcols];
                for (int i = 1; i <= numcols; ++i) {
                    result[i - 1] = rset.getString(i);
                }
                results.add(result);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            try {
                if (null != rset) {
                    rset.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                if (null != stmt) {
                    stmt.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                if (null != conn) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            dataSource.close();
        }
        for (int i = 0; i < results.size(); i++) {
			logger.info("获取到的结果为："+Arrays.toString(results.get(i)).toString());
		}
        return results;
    }
    
//updateDB

    public static void updateDB(String sqlId) throws Exception {
    	ApplicationContext ac = new ClassPathXmlApplicationContext(
				new String[] { "spring.xml", "spring-hibernate.xml" });
		httpCaseService = (HttpCaseServiceI) ac.getBean("httpCaseService");
		DBSql dbSql = httpCaseService.getDBSqlById(Integer.parseInt(sqlId.split("-")[1]));
		DBLink dbLink = httpCaseService.getDBLinkById(dbSql.getDbLinkId());
		String driverClassName = "";
		String url = "";
		if(dbLink.getType().equalsIgnoreCase("mysql")){
			driverClassName="com.mysql.jdbc.Driver";
			url="jdbc:mysql://"+dbLink.getIp()+":"+dbLink.getPort()+"/"+dbLink.getDbName()+"";
		}else if(dbLink.getType().equalsIgnoreCase("sqlserver")){
			driverClassName="net.sourceforge.jtds.jdbc.Driver";
			url="jdbc:jtds:sqlserver://"+dbLink.getIp()+";DatabaseName="+dbLink.getDbName()+"";
		}
        String username = dbLink.getUserName();
        String password = dbLink.getPassword();
        String sql = dbSql.getSql();
       DBUtils.updateDB(driverClassName, url,
                username, password, sql);
    }

    public static void updateDB(String driverClassName, String url,
            String username, String password, String sql) throws Exception {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(driverClassName);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setConnectionProperties(properties);
        updateDB(dataSource, sql);
    }
    
    public static void updateDB(BasicDataSource dataSource, String sql) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;

        try {
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            Formatter f = new BasicFormatterImpl();
            try {
                logger.info(String.format("执行SQL语句: %s", f.format(sql)));
            } catch (Exception ex) {
                logger.info(String.format("执行SQL语句：%s", sql));
            }
            rset = stmt.executeQuery(sql);
        } catch (SQLException e) {
            throw e;
        } finally {
            try {
                if (null != rset) {
                    rset.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                if (null != stmt) {
                    stmt.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                if (null != conn) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            dataSource.close();
        }
    }
}
